﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Mvc;
using System.Web.SessionState;
using System.Web.UI.WebControls;
using GWEB.WebBase.Data;
using GWEB.WebBase.Entity;
using GWEB.WebBase.Utility;
using GWEB.WebFrame.Models;
using GWEB.WebFrame.Public;
using Newtonsoft.Json;

namespace GWEB.WebFrame.Account
{
    public class AtController : PageInfoBase
    {
       
        [ActionFilter(IsLogin = false)]
        public ActionResult Index(String id, String keyCode)
        {
            string sql = string.Format("select did,dname,typeid from tb_sys_dictionary where typeid='D15' and did like '001%'");
            if (id != "-1")
                sql = string.Format("select did,dname,typeid from tb_sys_dictionary where typeid='D15'  and SUBSTRING(did,1,3)=SUBSTRING((select flowingtype from cu_Account where id={0}),1,3) ", id);
            ViewBag.FlowingType = BasePage.bd.ExecuteTable(sql);
            sql = string.Format("select did,dname,typeid from tb_sys_dictionary where typeid='D16'");
            ViewBag.AccountMode = BasePage.bd.ExecuteTable(sql);
            return Load("At", "Account/At", id, keyCode);
        }
        public string GetAcNumber()
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            string Years = String.Format("{0:000}", Convert.ToInt16(leagueId))+ System.DateTime.Now.Year.ToString() + String.Format("{0:00}", Convert.ToInt16(System.DateTime.Now.Month));
            string sql = "select right(replicate('0',13)+ltrim(CONVERT(varchar(13), convert(numeric ,isnull(max(AcNumber),'{0}'+'0000'))+1 )),13)  as Acnumber from cu_Account where Acnumber like '{0}%' and LeagueId=" + leagueId;
            //DataTable dt = BasePage.bd.ExecuteTable(string.Format(sql,Years));
            //return dt.Rows[0]["Acnumber"].ToString();
           // return Years;
             return  BasePage.bd.ExecuteScalarString(string.Format(sql,Years));
            
        }
      
        protected override SqlState SqlExecute(BaseData bd, String id, Dictionary<String, Object> data)
        {
            string htje = data["HtSfje"].ToString();
            string htsjje = data["HtSjje"].ToString();
            string FlowingType = data["FlowingType"].ToString();
         
            data.Add("AcNumber", GetAcNumber());
            data.Remove("HtSfje");
            data.Remove("HtSjje");
            
            int Khid = 0;
            try
            {
                Khid = int.Parse(Request["cid"]);

            }
            catch { }

            if (Khid>0)
                data["state"] = "0";
                String[] sql = new String[4];
            sql[0] = BaseData.MapToSql("cu_Account", "id", id, data);
            if(FlowingType=="00102001")
            { 
                if (data["ContractId"].ToString() != "") { 
                    sql[1] = string.Format("update cu_Account set state='3',ContractId={1} where  cid={0}  and FlowingType in ('00101001','00201001') and state=2 ", data["cid"].ToString(),data["ContractId"].ToString());
                     sql[2] = string.Format("update cu_Customers set CustState='6',ContractDate=getdate() where  id={0}", data["cid"].ToString());
                    try
                    { 
                    decimal HtSfje = decimal.Parse(htje);
                    decimal Bcje = decimal.Parse(data["Account"].ToString());
                    decimal je = decimal.Parse(htsjje);
                    if (HtSfje + Bcje >= je)
                    {
                        if (Khid>0)
                            sql[3] = string.Format("update cu_Contract set status=1 where id={0}", data["ContractId"].ToString());
                        else
                            sql[3] = string.Format("update cu_Contract set status=2 where id={0}", data["ContractId"].ToString());
                    }
                    }
                    catch { }
                }
            }
            if (FlowingType == "00202001")
            {
                sql[1] = string.Format("update cu_Contract set status=5 where id={0}", data["ContractId"].ToString());
            }
            if (FlowingType == "00101001")
            {
              
                sql[1] = string.Format("update cu_Customers set CustState='5',depositDate=getdate(),depositId={1} where CustState<>'6' and  id={0}", data["cid"].ToString(),data["CreateId"].ToString());
              
            }
            if (FlowingType == "00103001" & data["activityId"].ToString() != "")
            {
                if(Khid>0)
                    sql[1] = string.Format("update cu_Activity_SignUp set state=2   where  Id= {0}",  data["activityId"].ToString());
                else
                    sql[1] = string.Format("update cu_Activity_SignUp set state=3   where  Id= {0}",  data["activityId"].ToString());

            }

            if (FlowingType == "00203001" & data["activityId"].ToString() != "")
            { 
               sql[1] = string.Format("update cu_Activity_SignUp set state=5   where  Id= {0}",  data["activityId"].ToString());
              
            }
            if (FlowingType == "00104001")
            {
                if(Khid==0 && data["FollowUPId"].ToString()!="")               
                  sql[1] = string.Format("update cu_Cus_followup set IsCharge=1, ChargeId=[id]  where id ={0}", data["FollowUPId"].ToString());

            }
            bool t = bd.ExecuteSQL(sql, -1, "cu_Account", "id", id);
            ViewBag.Newid  = bd.GetInsertId;
          
            return t ? SqlState.True : SqlState.False;
        }
        [ActionFilter(IsLogin = false)]
        public JsonResult Data(String type, String id, String keyCode,string Tel,string khid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }

            String sql = @"      
               select cu_Customers.id,CustName,ChildName,CustTel, cu_Customers.LinkName,LinkRelation,LatelyReceId,Sex,
              isnull((select sshowname from tb_sys_users where iuserid=LatelyReceId),'未设置') UserName,
             Htmx.id ContractId,case when Htmx.id IS null then '' else '合同编号：'+ contractNumber+'套餐名称：' +SetmealName+'合同金额：'+CONVERT(varchar(10),contractprice)+' 实收：'+CONVERT(varchar(10),DisPrice)+' 订金：'+CONVERT(varchar(10),DjWsyJe)+' 已收:'+convert(varchar(10),isnull(CidHt.Htssje,0))+'本次应收：'+convert(varchar(10),DisPrice-DjWsyJe- isnull(CidHt.Htssje,0)) end Htxx,isnull(DisPrice-DjWsyJe- isnull(CidHt.Htssje,0),0) Htysje,isnull(CidHt.Htssje,0) HtSfje,isnull(DisPrice,0) DisPrice,
                          Hdmx.ActivityId ActivityId,case when Hdmx.id IS null then '' else  '活动金额：'+ convert(varchar(10),Decprice)+' 已收:'+convert(varchar(10),isnull((select SUM(account) from cu_Account where cu_Account.activityId=Hdmx.id and cu_Account.state<=2),0))+'本次应收：'+convert(varchar(10),Decprice-isnull((select SUM(account) from cu_Account where cu_Account.activityId=Hdmx.id and cu_Account.state<=2),0)) end Hdxx,Decprice-isnull((select SUM(account) from cu_Account where cu_Account.activityId=Hdmx.id and cu_Account.state<=2),0) Hdysje,
                Upmx.Id UpId,case when Upmx.id IS null then '无试听课程' else  '试听日期：'+ convert(varchar(10),MakeDate,120) end Upmx,
              isnull(Ht.HtYsje,0) Htje,isnull(Ht.HtSsje,0) HtSsje,isnull(Ht.HtSsJe,0)-ISNULL(Dj.Htje,0)-isnull(dj.DjYsyJe,0) HtWjfJe,isnull(Dj.HtWrzJe,0) HtWrzJe,   isnull(Dj.HtRzJe,0) HtRzJe,   -isnull(Dj.HtTfJe ,0)  HtTfJe ,   isnull(Dj.Djje,0) Djje , isnull(Dj.DjWrzJe,0) DjWrzJe , isnull(Dj.DjWsyJe,0) DjWsyJe, isnull(DjYsyJe,0) DjYsyJe , isnull(Dj.DjZfJe,0) DjZfJe, isnull(Hd.Hdje,0) Hdje,isnull(HdWsfJe,0)-ISNULL(Dj.Hdje,0) HdWsfJe, isnull(Dj.HdWrzJe,0) HdWrzJe,  isnull(Dj.HdRzJe,0) HdYrzJe, isnull(Hd.Hdsl,0) Hdsl , isnull(dj.HdTfJe,0) HdTfJe from  cu_Customers
              left join
              (select   *,ROW_NUMBER() OVER(PARTITION BY cid order by id desc) number from cu_Contract where  status in (0,1,5,6) )  Htmx on Htmx.Cid=cu_Customers.id
               left join
              (select   *,ROW_NUMBER() OVER(PARTITION BY cid order by id desc) num from cu_Activity_SignUp where  state<=1 )  Hdmx on Hdmx.Cid=cu_Customers.id
              left join
              (select   *,ROW_NUMBER() OVER(PARTITION BY cid order by id desc) Upnum from cu_Cus_followup where   ChargeId is null and IsCharge=0 and IsAuditions=1 )  UPmx on UPmx.Cid=cu_Customers.id
              left join 
              (select cid,count(*) sl, isnull(SUM(contractprice),0) HtYsje,isnull(SUM(DisPrice),0) HtSsje
              from cu_Contract  group by cid) Ht on Ht.Cid=cu_Customers.id
               left join 
              (select cid, SUM(case when FlowingType in ('00101001','00201001') then Account else 0 end) Djje,
              sum(case when   cu_Account.state<2 and  FlowingType in ('00101001','00201001') then Account else 0 end ) DjWrzJe,  
               sum(case when   cu_Account.state=2 and  FlowingType in ('00101001','00201001') then Account else 0 end ) DjWsyJe,  
               sum(case when   cu_Account.state=3  and  FlowingType in ('00101001','00201001')then Account else 0 end ) DjYsyJe,  
                 sum(case when   cu_Account.state=4  and  FlowingType in ('00101001','00201001')then Account else 0 end ) DjZfJe,
                 SUM(case when FlowingType in ('00102001','00202001') and cu_Account.state<>4 then Account else 0 end) Htje,
              sum(case when   cu_Account.state<2 and  FlowingType in ('00102001','00202001') then Account else 0 end ) HtWrzJe,  
               sum(case when   cu_Account.state=2 and  FlowingType ='00102001' then Account else 0 end ) HtRzJe,            
                 sum(case when   cu_Account.state=2  and  FlowingType ='00202001'then Account else 0 end ) HtTfJe,
                  SUM(case when FlowingType in ('00103001','00203001') and cu_Account.state<>4 then Account else 0 end) Hdje,
              sum(case when   cu_Account.state<2 and  FlowingType in ('00103001','00203001') then Account else 0 end ) HdWrzJe,  
               sum(case when   cu_Account.state=2 and  FlowingType ='00103001' then Account else 0 end ) HdRzJe,            
                 sum(case when   cu_Account.state=2  and  FlowingType ='00203001' then Account else 0 end ) HdTfJe
              from cu_Account   group by cid) Dj on Dj.Cid=cu_Customers.id
                left join 
              (select cid, SUM(Decprice) Hdje,COUNT(*) Hdsl,
              sum(case when   cu_Activity_SignUp.state<2 then Decprice else 0 end ) HdWsfJe,  
               sum(case when   cu_Activity_SignUp.state=2 then Decprice else 0 end ) HdWrzJe,  
               sum(case when   cu_Activity_SignUp.state>2 and state<6 then Decprice else 0 end ) HdYrzJe,
                 sum(case when   cu_Activity_SignUp.state=6 then Decprice else 0 end ) HdTfJe  
              from cu_Activity_SignUp    group by cid) Hd on Hd.Cid=cu_Customers.id   
                 left join 
(select SUM(account) Htssje, cid,ContractId from cu_Account where (cu_Account.state<=2 and  FlowingType in ('00102001','00202001')) or (cu_Account.state=3 and  FlowingType in ('00101001','00201001')) group by cid,ContractId  ) CidHt on CidHt.cid=cu_Customers.id and CidHt.ContractId=Htmx.id 
             where {0} and cu_Customers.LeagueId='{1}'  and ISNULL(number,0)<=1 and ISNULL(num,0)<=1 and isnull(Upnum,0)<=1 ";
            if (id == "-1")
            {
                if (int.Parse(khid) > 0)
                    sql = string.Format(sql, "cu_Customers.id=" + khid,leagueId);
                else
                sql = string.Format(sql, "CustTel like '%" +Tel + "%'", leagueId); }
            else
            {
             
                    string Cid = BasePage.bd.ExecuteScalarString("select cid from cu_account where id=" + id);
                    sql = string.Format(sql, "cu_Customers.id=" + Cid, leagueId);
             
            }
            BasePage.bd.ColumnNameToLower = false;
            String str = JsonConvert.SerializeObject(BasePage.bd.ExecuteTable(sql));
            
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);            
            return BasePage.Json(ResultData.OK(data));

        }
        [ActionFilter(IsLogin = true)]
        public JsonResult HdData(String type, String id, String keyCode,string Tel)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = "select  '' LatelyReceId, '' id, id ActivityId,ActivityName,ChargeName,ActAddress,MPrice,Classhour,NoMPrice,tel,convert(varchar(10),isnull((select count(*) from cu_Activity_SignUp where cu_Activity_SignUp.ActivityId=cu_Activity.id),0))+'/'+CONVERT(varchar(10),signnumber) sl " +
                "from cu_Activity " +
                "where state=1 and {0} and LeagueId='{1}' ";
            if (id == "-1")
            { sql = string.Format(sql, "ActivityName like '%" +Tel + "%'", leagueId); }
            else
            {
                string Cid = BasePage.bd.ExecuteScalarString("select  ActivityId from cu_account where id=" + id);
                sql = string.Format(sql, "cu_Activity.id=" + Cid);
            }
            BasePage.bd.ColumnNameToLower = false;
            String str = JsonConvert.SerializeObject(BasePage.bd.ExecuteTable(sql));
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            return BasePage.Json(ResultData.OK(data));

        }
        [ActionFilter(IsLogin = true)]
        public JsonResult RySkData(String type, String id, String keyCode, string Cid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = @" select cu_TeacherCourse_Sign.id,Godate,tb_sys_dictionary.dname stateName,IsAuditions,AchievementName,CourseName,TeachDate,TeacherName,TwoTeacherName, starttime,endTime,DecClass,Decprice from cu_TeacherCourse_Sign
left join cu_TeacherCourse on cu_TeacherCourse.id=cu_TeacherCourse_Sign.Tid
left join tb_sys_dictionary on tb_sys_dictionary.did=cu_TeacherCourse_Sign.state and tb_sys_dictionary.typeid='D21'
    where cu_TeacherCourse_Sign.cid = '" + Cid + "'  and cu_TeacherCourse_Sign.LeagueId='" + leagueId + "' order by cu_TeacherCourse_Sign.id desc ";
            BasePage.bd.ColumnNameToLower = false;
            DataTable dtall = BasePage.bd.ExecuteTable(sql);
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("count", dtall.Rows.Count);
            int PageIndex = int.Parse(Request.QueryString["page"]);
            int PageSize = int.Parse(Request.QueryString["limit"]);
            DataTable dt = GetPagedTable(dtall, PageIndex, PageSize);
            String str = JsonConvert.SerializeObject(dt);
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data1 = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            data.Add("data", data1);
            return BasePage.Json(ResultData.OK(data));

        }
        [ActionFilter(IsLogin = true)]
        public JsonResult StData(String type, String id, String keyCode, string Cid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = @"select cu_Cus_followup.id,convert(varchar(10),MakeDate,120) MakeDate,cu_Course.CourseName,cu_Cus_followup.Remark,cu_TeacherCourse.TeacherName,cu_TeacherCourse.TwoTeacherName from cu_Cus_followup
left join
cu_Course on cu_Cus_followup.CourseId=cu_Course.id
left join 
cu_TeacherCourse on CouTeachId=cu_TeacherCourse.id
where ChargeId is null and IsCharge=0 and IsAuditions=1 and Cid={0} and cu_Cus_followup.LeagueId='{1}' ";
            BasePage.bd.ColumnNameToLower = false;
            DataTable dtall = BasePage.bd.ExecuteTable(string.Format(sql,Cid, leagueId));
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("count", dtall.Rows.Count);
            int PageIndex = int.Parse(Request.QueryString["page"]);
            int PageSize = int.Parse(Request.QueryString["limit"]);
            DataTable dt = GetPagedTable(dtall, PageIndex, PageSize);
            String str = JsonConvert.SerializeObject(dt);
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data1 = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            data.Add("data", data1);
            return BasePage.Json(ResultData.OK(data));

        }

        [ActionFilter(IsLogin = true)]
        public JsonResult DjData(String type, String id, String keyCode,string Cid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = @"select  cu_Account.id,D16.dname FlowingName,D15.dname ModeName,Account,convert(varchar(10),ADate,120) ADate,isnull(sshowname,'未设置') AchName,flowingtype,cu_Account.state,cu_Account.isReturn,isnull(cu_Account.ContractId,-1) ContractId,isnull(cu_Account.activityId,-1) activityId ,isnull(cu_Account.FollowUPId,-1) FollowUPId,
 case when  cu_Contract.CourseEnd is null then 0  when  cu_Contract.CourseEnd>GETDATE() then 0 else  1 end htstate,                
case cu_Account.state when 0 then '未入帐' when 1 then '已提交未审核' when 2 then '已入帐' when 3 then '已使用' when 4 then '已作废' end stateName 
             from cu_Account
             left join
             tb_sys_users on  iuserid = AchievementId
             left join
             tb_sys_dictionary D16 on d16.did = AccountMode and d16.typeid = 'D16'
             left join
             tb_sys_dictionary D15 on d15.did = FlowingType and d15.typeid = 'D15'
             left join 
             cu_Contract on cu_Contract.id=cu_Account.ContractId
               where  cu_Account.cid = '" + Cid+ "'  and cu_Account.LeagueId='" + leagueId + "' ";
            BasePage.bd.ColumnNameToLower = false;
            DataTable dtall = BasePage.bd.ExecuteTable(sql);
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("count", dtall.Rows.Count);
            int PageIndex = int.Parse(Request.QueryString["page"]);
            int PageSize = int.Parse(Request.QueryString["limit"]);
            DataTable dt = GetPagedTable(dtall, PageIndex, PageSize);
            String str = JsonConvert.SerializeObject(dt);
               BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data1 = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            data.Add("data", data1);
            return BasePage.Json(ResultData.OK(data));

        }
        public DataTable GetPagedTable(DataTable dt, int PageIndex, int PageSize)//PageIndex表示第几页，PageSize表示每页的记录数
        {
            if (PageIndex == 0)
                return dt;//0页代表每页数据，直接返回

            DataTable newdt = dt.Copy();
            newdt.Clear();//copy dt的框架

            int rowbegin = (PageIndex - 1) * PageSize;
            int rowend = PageIndex * PageSize;

            if (rowbegin >= dt.Rows.Count)
                return newdt;//源数据记录数小于等于要显示的记录，直接返回dt

            if (rowend > dt.Rows.Count)
                rowend = dt.Rows.Count;
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }
            return newdt;
        }
        [ActionFilter(IsLogin = true)]
        public JsonResult RyHtData(String type, String id, String keyCode,string Cid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = @" select id, contractNumber,SetmealName,ContractPrice,DisPrice,convert(varchar(10),CreateDate,120) CreateDate,isnull(sshowname,'未设置') AchName,
                 case state when 0 then '未提交' when 1 then '未审核' when 2 then '已审核' when 3 then '3审核未通过'  end stateName,
                    case status when 0 then '已登记未收费' when 1 then '已登记费用未入账' when 5 then '已登记退费未出账' when 6 then '已退费' else '已入账' end statusName,
                   convert(varchar(10), isnull(Htje,0))+'/'+convert(varchar(10),isnull(DisPrice,0))+'/'+convert(varchar(10),isnull(ContractPrice,0)) je,
                   convert(varchar(10), Constart,120)+'至'+  convert(varchar(10), ConEnd,120) ConDate, case when CourseStart is null  then '未设置' else   convert(varchar(10),CourseStart,120)+'至'+  convert(varchar(10), CourseEnd,120) end CourseDate,
                        '合同编号：'+ contractNumber+'套餐名称：' +SetmealName+'合同金额：'+CONVERT(varchar(10),contractprice)+' 实收：'+CONVERT(varchar(10),DisPrice)+' 订金：'+CONVERT(varchar(10),DjWsyJe)+' 已收:'+convert(varchar(10),isnull(Htje,0))+'本次应收：'+convert(varchar(10),DisPrice-isnull(DjWsyJe,0)-isnull(htje,0))  Htxx,DisPrice-isnull(DjWsyJe,0)-isnull(htje,0) Htysje,isnull(htje,0) HtSfje
               
                    from cu_Contract
                left join
             tb_sys_users on iuserid = AchievementId 
             left join
                (select cid, SUM(case when FlowingType in ('00101001','00201001') then Account else 0 end) Djje,
              sum(case when   cu_Account.state<2 and  FlowingType in ('00101001','00201001') then Account else 0 end ) DjWrzJe,  
               sum(case when   cu_Account.state=2 and  FlowingType in ('00101001','00201001') then Account else 0 end ) DjWsyJe,  
               sum(case when   cu_Account.state=3  and  FlowingType in ('00101001','00201001')then Account else 0 end ) DjYsyJe,  
                 sum(case when   cu_Account.state=4  and  FlowingType in ('00101001','00201001')then Account else 0 end ) DjZfJe
                
              from cu_Account   group by cid) Dj  on Dj.cid=cu_Contract.Cid  
             left join 
              (select cid,ContractId,SUM(Account) Htje            
              from cu_Account where (cu_Account.state<=2 and  FlowingType in ('00102001','00202001')) or (cu_Account.state=3 and  FlowingType in ('00101001','00201001')) group by cid,ContractId) Ht  on Ht.ContractId=cu_Contract.id  
           where  cu_Contract.cid = '" + Cid + "'  and cu_Contract.LeagueId='" + leagueId + "' order by id desc ";
            BasePage.bd.ColumnNameToLower = false;
            DataTable dtall = BasePage.bd.ExecuteTable(sql);
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("count", dtall.Rows.Count);
            int PageIndex = int.Parse(Request.QueryString["page"]);
            int PageSize = int.Parse(Request.QueryString["limit"]);
            DataTable dt = GetPagedTable(dtall, PageIndex, PageSize);
            String str = JsonConvert.SerializeObject(dt);
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data1 = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            data.Add("data", data1);
            return BasePage.Json(ResultData.OK(data));

        }

        [ActionFilter(IsLogin = true)]
        public JsonResult RyHdData(String type, String id, String keyCode,string Cid)
        {
            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            String sql = @"select id,DecPrice,convert(varchar(10),CreateDate,120) CreateDate,(select ActivityName from cu_Activity where cu_Activity.id=cu_Activity_SignUp.ActivityId)  ActivityName,
               case   when state<2 then '未收费' when state=2 then '已确认未入账' when state>2 and state<5 then '已缴费' when state=6 then '已退费' else '已提交退费审请'  end stateName,  	
                  '活动金额：'+ convert(varchar(10),Decprice)+' 已收:'+convert(varchar(10),isnull(Hdje,0))+'本次应收：'+convert(varchar(10),Decprice-isnull(Hdje,0))   Hdxx,Decprice-isnull(Hdje,0) Hdysje
            
                from cu_Activity_SignUp
                 left join 
              (select cid,activityId,SUM(case when FlowingType in ('00103001','00203001') and cu_Account.state<>4 then Account else 0 end) Hdje            
              from cu_Account   group by cid,activityId) Hd  on Hd.activityId=cu_Activity_SignUp.id 
       
           where   cu_Activity_SignUp.cid = '" +Cid + "'  and cu_Activity_SignUp.LeagueId='" + leagueId + "' order by id desc ";
            BasePage.bd.ColumnNameToLower = false;
            DataTable dtall = BasePage.bd.ExecuteTable(sql);
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("count", dtall.Rows.Count);
            int PageIndex = int.Parse(Request.QueryString["page"]);
            int PageSize = int.Parse(Request.QueryString["limit"]);
            DataTable dt = GetPagedTable(dtall, PageIndex, PageSize);
            String str = JsonConvert.SerializeObject(dt);
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data1 = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            data.Add("data", data1);
            return BasePage.Json(ResultData.OK(data));

        }
        [ActionFilter(IsLogin = true)]
        public JsonResult FlowingTypeData(String type, String id, String keyCode,string lxmc)
        {
            PageInfo pg = isBrowse(type, id, keyCode);
            if (pg == null)
            {
                return BasePage.Json(ResultData.Error(WebMessage.NoBrowse));
            }
            string Lx = "";
            if (lxmc == "收入类")
                Lx = "001";
            if (lxmc == "支出类")
                Lx = "002";
            if (lxmc == "预算")
                Lx = "003";
            String sql = "select did,dname,typeid from tb_sys_dictionary where typeid='D15' and  did like '" + Lx + "%' ";
            BasePage.bd.ColumnNameToLower = false;
            String str = JsonConvert.SerializeObject(BasePage.bd.ExecuteTable(sql));
            BasePage.bd.ColumnNameToLower = true;
            List<Dictionary<String, Object>> data = JsonConvert.DeserializeObject<List<Dictionary<String, Object>>>(str);
            return BasePage.Json(ResultData.OK(data));

        }

        [ActionFilter(IsJson =   true)]
        public JsonResult UpdateState(String id, String keyCode, String state)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Code("0001","您没有权限"));

            BaseData bd = new BaseData();
          string UserId=  BasePage.GetUserId(Session);
            //   String sql = "update cu_account set state={0},auditingid='" + UserId + "',auditingdate='"+System.DateTime.Now.ToString()+"' where id={1}";
            String sql = @"select FlowingType,cu_account.state,isnull(cu_account.ContractId,0) ContractId,isnull(activityId,0) activityId,isnull(cu_account.cid,0) cid,isnull(account,0) je ,isnull(djje,0) dj,isnull(htje,0) ht,isnull(hdje,0) hd,isnull(stje,0) st,isnull(RefundId,-1) RefundId,isnull(isReturn,0) isReturn,isnull(djsyje,0) djsyje,isnull(htssje,0) htssje,isnull(DisPrice,0) DisPrice  from  cu_account
left join
 (select SUM( case when a.FlowingType in ('00101001', '00201001')  then account else 0 end ) djje,
 SUM( case when a.FlowingType in ('00102001', '00202001')  then account else 0 end) htje,
 SUM( case when a.FlowingType in ('00103001', '00203001')  then account else 0 end) hdje,
  SUM( case when a.FlowingType in ('00104001', '00204001')  then account else 0 end) stje,
 cid from cu_Account a where a.state <= 2    group by cid) Cu_sum on Cu_sum.cid = cu_account.cid
 left join 
(select  SUM(case when  FlowingType in ('00101001','00201001')  and state=3 then Account else 0 end ) djsyje,SUM(case when  FlowingType in ('00102001','00202001')  and state=2 then Account else 0 end ) htssje,ContractId from cu_Account  group by ContractId) sydj on sydj.ContractId=cu_Account.ContractId
left join
cu_Contract on cu_Contract.id=cu_Account.ContractId
 where cu_account.id =  {0} ";
            DataTable dt = BasePage.bd.ExecuteTable(string.Format(sql,id));
            String[] ExeSql = new String[4];
            if (dt != null)
            {
                ExeSql[0] = "update cu_account set state="+state+",auditingid='" + UserId + "',auditingdate='" + System.DateTime.Now.ToString() + "' where id="+id;
                switch (dt.Rows[0]["FlowingType"].ToString())
                    {
                    case "00101001":
                        if(state=="4"&&decimal.Parse(dt.Rows[0]["je"].ToString())>=decimal.Parse(dt.Rows[0]["dj"].ToString()))
                            ExeSql[1] = "update cu_Customers set CustState=3  where id=" + dt.Rows[0]["cid"].ToString();                   
                        break;
                    case "00201001":
                        if (state == "4" && decimal.Parse(dt.Rows[0]["dj"].ToString()) >= 0)
                        {
                            ExeSql[1] = "update cu_Customers set CustState=5  where CustState<>6 and id=" + dt.Rows[0]["cid"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        if (state == "2" && decimal.Parse(dt.Rows[0]["dj"].ToString()) <= 0)
                        {
                            ExeSql[1] = "update cu_Customers set CustState=9  where id=" + dt.Rows[0]["cid"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=1  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        break;
                    case "00102001":
                        if (state == "4")
                        {
                            ExeSql[1] = "update cu_Customers set CustState=3  where id=" + dt.Rows[0]["cid"].ToString();                         
                            ExeSql[2] = "update cu_Contract set status=0  where id=" + dt.Rows[0]["ContractId"].ToString();
                        }
                        if (state == "2"&& dt.Rows[0]["ContractId"].ToString()!="0")
                        {
                            decimal htssje = 0;
                            decimal djje = 0;
                            decimal htje = 0;
                           
                            try {
                                htssje = decimal.Parse(dt.Rows[0]["htssje"].ToString());//已收合同
                                djje = decimal.Parse(dt.Rows[0]["djsyje"].ToString());//订金金额
                                htje = decimal.Parse(dt.Rows[0]["DisPrice"].ToString());//合同金额
                            
                            }
                            catch { }
                            if(htssje+djje>=htje)
                            { 
                                 ExeSql[1] = "update cu_Contract set status=2  where id=" + dt.Rows[0]["ContractId"].ToString();
                                 ExeSql[2] = "update cu_Customers set CustState=6  where id=" + dt.Rows[0]["cid"].ToString();
                            }

                        }
                        break;
                    case "00202001":
                        if (state == "4")
                        {

                            decimal htssje = 0;
                            decimal djje = 0;
                            decimal htje = 0;
                            decimal bcje = 0;
                            try
                            {
                                htssje = decimal.Parse(dt.Rows[0]["htssje"].ToString());//已收合同
                                djje = decimal.Parse(dt.Rows[0]["djsyje"].ToString());//订金金额
                                htje = decimal.Parse(dt.Rows[0]["DisPrice"].ToString());//合同金额
                                bcje = decimal.Parse(dt.Rows[0]["account"].ToString());//本次金额
                            }
                            catch { }
                            if (htssje + djje + bcje >= htje)
                            { ExeSql[2] = "update cu_Customers set CustState=6  where id=" + dt.Rows[0]["cid"].ToString();
                              ExeSql[3] = "update cu_Contract set status=2  where id=" + dt.Rows[0]["ContractId"].ToString();
                            }else
                            { 
                                ExeSql[2] = "update cu_Contract set status=1  where id=" + dt.Rows[0]["ContractId"].ToString();
                            }
                            ExeSql[1] = "update  cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        if (state == "2")
                        {
                            ExeSql[1] = "update cu_Customers set CustState=10  where id=" + dt.Rows[0]["cid"].ToString();
                            ExeSql[2] = "update cu_Contract set status=6  where id=" + dt.Rows[0]["ContractId"].ToString();
                            ExeSql[3] = "update cu_account set isReturn=1  where ContractId=" + dt.Rows[0]["ContractId"].ToString();
                        }                     
                        break;
                    case "00103001":
                        if (state == "4")
                        {
                            
                            ExeSql[1] = "update cu_Activity_SignUp set state=1  where  Id= " +dt.Rows[0]["ActivityId"].ToString();
                        }
                        if (state == "2")
                        {
                            ExeSql[1] = "update cu_Activity_SignUp set state=3  where  Id= " + dt.Rows[0]["ActivityId"].ToString();
                        }
                        break;
                    case "00203001":
                        if (state == "4")
                        {
                            ExeSql[1] = "update cu_Activity_SignUp set state=3 where  Id= " + dt.Rows[0]["ActivityId"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        if (state == "2" )
                        {
                            ExeSql[1] = "update cu_Activity_SignUp set state=6  where Id= " + dt.Rows[0]["ActivityId"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=1  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        break;
                    case "00104001":
                        if (state == "4")
                        {

                            ExeSql[1] = "update cu_Cus_followup set IsCharge=0  where  ChargeId=" +id;
                        }
                        
                        break;
                    case "00204001":
                        if (state == "4")
                        {
                            ExeSql[1] = "update cu_Cus_followup set IsCharge=1  where ChargeId=" + dt.Rows[0]["RefundId"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        if (state == "2")
                        {
                            ExeSql[1] = "update cu_Cus_followup set  IsCharge=0  where ChargeId=" + dt.Rows[0]["RefundId"].ToString();
                            ExeSql[2] = "update cu_account set isReturn=1  where id=" + dt.Rows[0]["RefundId"].ToString();
                        }
                        break;
                }
            }
            else
                return BasePage.Json(ResultData.Code("0001", WebMessage.SaveFailure));

            bool t = bd.ExecuteSQL(ExeSql);
            if (t)
                return BasePage.Json(ResultData.Code("0000", WebMessage.SaveSuccess));
            else
                return BasePage.Json(ResultData.Code("0001", WebMessage.SaveFailure));
        }
        [ActionFilter(IsJson = true)]
        public JsonResult DelAccount(String id, String keyCode)
        {
            if (!Fun.IDVerify(id, keyCode))
                return BasePage.Json(ResultData.Code("0001", "您没有权限"));

            BaseData bd = new BaseData();
            string UserId = BasePage.GetUserId(Session);

            String sql = @"select FlowingType,cu_account.state,isnull(cu_account.ContractId,0) ContractId,isnull(activityId,0) activityId,isnull(cu_account.cid,0) cid,isnull(account,0) je ,isnull(djje,0) dj,isnull(htje,0) ht,isnull(hdje,0) hd,isnull(stje,0) st,isnull(RefundId,-1) RefundId,isnull(isReturn,0) isReturn,isnull(djsyje,0) djsyje,isnull(htssje,0) htssje,isnull(DisPrice,0) DisPrice  from  cu_account
left join
 (select SUM( case when a.FlowingType in ('00101001', '00201001')  then account else 0 end ) djje,
 SUM( case when a.FlowingType in ('00102001', '00202001')  then account else 0 end) htje,
 SUM( case when a.FlowingType in ('00103001', '00203001')  then account else 0 end) hdje,
  SUM( case when a.FlowingType in ('00104001', '00204001')  then account else 0 end) stje,
 cid from cu_Account a where a.state <= 2    group by cid) Cu_sum on Cu_sum.cid = cu_account.cid
 left join 
(select  SUM(case when  FlowingType in ('00101001','00201001')  and state=3 then Account else 0 end ) djsyje,SUM(case when  FlowingType in ('00102001','00202001')  and state=2 then Account else 0 end ) htssje,ContractId from cu_Account where cu_Account.ContractId=20  group by ContractId) sydj on sydj.ContractId=cu_Account.ContractId
left join
cu_Contract on cu_Contract.id=cu_Account.ContractId
 where cu_account.id =  {0} ";
            DataTable dt = BasePage.bd.ExecuteTable(string.Format(sql, id));
            String[] ExeSql = new String[4];
            if (dt != null)
            {
                ExeSql[0] = "delete cu_account  where id=" + id;
                switch (dt.Rows[0]["FlowingType"].ToString())
                {
                    case "00101001":
                        if (decimal.Parse(dt.Rows[0]["je"].ToString()) >= decimal.Parse(dt.Rows[0]["dj"].ToString()))
                            ExeSql[1] = "update cu_Customers set CustState=3  where id=" + dt.Rows[0]["cid"].ToString();                        
                        break;
                 
                    case "00102001":
                        ExeSql[1] = "update cu_Customers set CustState=3  where id=" + dt.Rows[0]["cid"].ToString();
                        ExeSql[2] = "update cu_Contract set status=0  where id=" + dt.Rows[0]["ContractId"].ToString();
                        break;
                  
                    case "00103001":
                       
                            ExeSql[1] = "update cu_Activity_SignUp set state=1  where Id= " + dt.Rows[0]["ActivityId"].ToString();
                        
                        break;
                    case "00203001":
                        ExeSql[1] = "update cu_Activity_SignUp set state=3 where  Id= " + dt.Rows[0]["ActivityId"].ToString();
                        ExeSql[2] = "update cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        break;
                    case "00104001":
                            ExeSql[1] = "update cu_Cus_followup set IsCharge=0,ChargeId=null  where ChargeId=" + id;

                        break;
                    case "00204001":                      
                        ExeSql[1] = "update cu_account set isReturn=0  where id=" + dt.Rows[0]["RefundId"].ToString();
                        break;
                }
            }
            else
                return BasePage.Json(ResultData.Code("0001", WebMessage.SaveFailure));

            bool t = bd.ExecuteSQL(ExeSql);

           
            if (t)
                return BasePage.Json(ResultData.Code("0000", WebMessage.SaveSuccess));
            else
                return BasePage.Json(ResultData.Code("0001", WebMessage.SaveFailure));
        }

        protected override void FieldAddInit(DataRow data)
        {
            data["CreateId"] = BasePage.GetUserId(Session);

            DataRow drUser = BasePage.GetUser(Session);
            String leagueId = Fun.cString(drUser["LeagueId"].ToString());
            data["LeagueId"] = leagueId;
            ViewBag.htysje = 0;
            data["guid"] = Guid.NewGuid().ToString("N");
            data["activityId"] = 0;
            ViewBag.Lx = 0;
            int Cid = 0;
            try
            { Cid = int.Parse(Request.QueryString["cid"]); }
            catch { };
            ViewBag.Tfyje = 0;
            ViewBag.Customer = Cid;
            data["AccountType"] = "收入类";
            data["state"] = "2";
            data["RefundId"] = "0";
            if (Cid > 0)
            {
                string strSql = "select * from  cu_Customers where id={0}";
                DataTable dt = BasePage.bd.ExecuteTable(string.Format(strSql, Cid));
                if (dt != null)
                {
                    data["AchievementId"] = dt.Rows[0]["LatelyReceId"].ToString();
                }
                try
                {
                    data["cid"] = Cid.ToString();
                    data["FlowingType"] = Request.QueryString["FlowingType"];
                    data["guid"] = Request.QueryString["guid"];
                    if (Request.QueryString["FlowingType"].Substring(0, 3) == "002")
                        data["AccountType"] = "支出类";
                    if (Request.QueryString["FlowingType"].Substring(0, 3) == "003")
                        data["AccountType"] = "预算";
                }
                catch
                {
                    data["FlowingType"] = "";

                }
                try
                { data["RefundId"] = Request.QueryString["accountId"]; }
                catch { data["RefundId"] = "0"; }
                if (data["FlowingType"].ToString() == "00103001"|| data["FlowingType"].ToString() == "00203001")
                { data["activityId"] = Request.QueryString["ActivityId"];
                    if (data["FlowingType"].ToString() == "00203001")
                    {
                        strSql = "select sum(Account) je,MAX(id) id from cu_Account where cid={0}  and guid='{1}' and activityId={2}  and state =2";
                        dt = BasePage.bd.ExecuteTable(string.Format(strSql, Cid, Request.QueryString["guid"], Request.QueryString["ActivityId"]));
                        decimal je = 0;
                        int id = -1;
                        if (dt != null)
                        {
                            try
                            {
                                je = decimal.Parse(dt.Rows[0]["je"].ToString());
                                id = int.Parse(dt.Rows[0]["id"].ToString());
                            }
                            catch { }
                        }
                        ViewBag.Tfyje = je;                    
                        data["RefundId"] = id;
                    }
                }

                if (data["FlowingType"].ToString() == "00102001")
                {
                    string Remark = "已收定金:";
                    decimal ssje = 0;

                     strSql = "select ADate,Account from cu_Account where cid={0}  and FlowingType in ('00101001','00201001') and state=2 ";
                     dt = BasePage.bd.ExecuteTable(string.Format(strSql, Cid));
                    int djzt = 0;
                    if (dt != null)
                    {
                        for (int rowsnumber = 0; rowsnumber < dt.Rows.Count; rowsnumber++)
                        {
                            ssje += decimal.Parse(dt.Rows[rowsnumber]["Account"].ToString());
                            Remark += "日期：" + dt.Rows[rowsnumber]["ADate"].ToString() + "金额：" + dt.Rows[rowsnumber]["Account"].ToString();
                            djzt = 1;

                        }
                    }
                    if (djzt == 0)
                        Remark += "无";
                    else
                        Remark += "合计:" + ssje.ToString();
                    Remark += "本合同已收:";
                    decimal htje = 0;
                    strSql = "select sum(Account) je from cu_Account where cid={0}  and guid='{1}' ";
                    dt = BasePage.bd.ExecuteTable(string.Format(strSql, Cid, data["guid"]));
                    if (dt != null)
                    {
                        try
                        {
                            htje = decimal.Parse(dt.Rows[0]["je"].ToString());
                        }
                        catch { }
                    }
                    if (htje == 0)
                    {
                        Remark += "无";
                    }
                    else
                        Remark += "合计:" + htje.ToString();
                    ViewBag.htysje = htje + ssje;
                    data["Remark"] = Remark;

                }
                if (data["FlowingType"].ToString() == "00202001")
                {
                    int Contractid = 0;
                    try
                    { Contractid = int.Parse(Request.QueryString["Contractid"]); }
                    catch { };
                     strSql = "select sum(Account) je,MAX(id) id from cu_Account where cid={0}  and contractId={1} and state in (2,3)";
                     dt = BasePage.bd.ExecuteTable(string.Format(strSql, Cid,Contractid));
                    decimal je = 0;
                    int id = -1;
                    if (dt != null)
                    {
                        try
                        {
                            je = decimal.Parse(dt.Rows[0]["je"].ToString());
                            id = int.Parse(dt.Rows[0]["id"].ToString());
                        }
                        catch { }
                    }
                    ViewBag.Tfyje = je;
                    ViewBag.htysje = je;
                    data["RefundId"] = id;
                }
            }
            else
            {
                data["FlowingType"] = "";

            }
           
                data["AccountMode"] = "";
            
                base.FieldAddInit(data);
            }
       
        protected override void FieldEditInit(DataRow data)
        {
          ViewBag.htysje  =0;
            ViewBag.Lx = 1;
            ViewBag.Customer = 0;
            ViewBag.Tfyje = 0;
            base.FieldEditInit(data);
        }

    }
}